//
//  NovelDatabase.m
//  数据库的使用
//
//  Created by Qiaofeng on 15/5/11.
//  Copyright (c) 2015年 Qiaofeng. All rights reserved.
//

#import "NovelDatabase.h"
#import "DB.h"
#import "NovelModel.h"
@implementation NovelDatabase
+ (void)panduan:(NSString *)str
{
    // 获得数据库指针
    
    sqlite3 *db = [DB openDB];
    int result = sqlite3_exec(db, [str UTF8String], nil, nil, nil);
    
    //判断是否执行成功
    
    if (result == SQLITE_OK) {
        NSLog(@"执行成功");
    }else
    {
        NSLog(@"执行失败");
    }
}
+ (void)insertWithModel:(NovelModel *)model
{
  // sql语句
    //    albumId
    //    categoryId
    //    caregoryName
    //    title
    //    uid
    //    nickname
    NSString *str = [NSString stringWithFormat:@"insert into NovelCollect(albumId,categoryId,categoryName,title,uid,nickname,coverLarge,playKind,loginName,albumIdMin) values(%ld,%ld,'%@','%@',%ld,'%@','%@','%@','%@',%ld)",model.albumId,model.categoryId,model.categoryName,model.title,model.uid,model.nickname,model.coverLarge,model.playKind,model.loginName,model.albumIdMin];
    [NovelDatabase panduan:str];
}


//删除一个
+ (void)deleteWithModel:(NovelModel *)model
{
  
    NSString *str = [NSString stringWithFormat:@"DELETE FROM NovelCollect WHERE albumIdMin=%ld AND loginName = '%@';",model.albumId,model.loginName];
  [NovelDatabase panduan:str];
}
//删除
+ (void)deleteWithModelAllLogin:(NovelModel *)model
{
    NSString *str = [NSString stringWithFormat:@"DELETE FROM NovelCollect WHERE loginName = '%@' AND playKind = '%@';",model.loginName,model.playKind];
    [NovelDatabase panduan:str];
}




//查询
+ (NSArray *)selectAll
{
    NSString *str = @"select albumId,categoryId,categoryName,title,uid,nickname,coverLarge,playKind,loginName,albumIdMin from NovelCollect";
 sqlite3 *db = [DB openDB];
    NSMutableArray *array = [NSMutableArray array];
    // 定义执行结果接收者
    sqlite3_stmt *stmt = nil;
    // 我们查询的结果就在stmt里面;
    int result = sqlite3_prepare(db, [str UTF8String], -1, &stmt, nil);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW){
            int albumId = sqlite3_column_int(stmt, 0);
            int categoryId = sqlite3_column_int(stmt, 1);
            const unsigned char *categoryName1 = sqlite3_column_text(stmt, 2);
            const unsigned char *title1 = sqlite3_column_text(stmt, 3);
            int uid = sqlite3_column_int(stmt, 4);
            const unsigned char *nickname1 = sqlite3_column_text(stmt, 5);
            const unsigned char *coverLarge1 = sqlite3_column_text(stmt, 6);
            const unsigned char *playKind1 = sqlite3_column_text(stmt, 7);
            const unsigned char *loginName1 = sqlite3_column_text(stmt, 8);
             int albumIdMin = sqlite3_column_int(stmt, 9);

            
            NSString *categoryName = [NSString stringWithUTF8String:(const char *)categoryName1];
            NSString *title = [NSString stringWithUTF8String:(const char *)title1];
            NSString *nickname = [NSString stringWithUTF8String:(const char *)nickname1];
            NSString *coverLarge = [NSString stringWithUTF8String:(const char *)coverLarge1];
            NSString *playKind = [NSString stringWithUTF8String:(const char *)playKind1];
            NSString *loginName = [NSString stringWithUTF8String:(const char *)loginName1];
            
            NovelModel *model = [NovelModel novelModelWithAlbumId:albumId categoryId:categoryId categoryName:categoryName title:title uid:uid nickname:nickname coverLarge:coverLarge playKind:playKind loginName:loginName albumIdMin:albumIdMin];
            [array addObject:model];
        }
    }
    return array;
}
//类的
+ (NSArray *)selectNamePlayKind:(NSString *)playKind loginName:(NSString *)loginName
{
    NSString *str = [NSString stringWithFormat:@"select albumId,categoryId,categoryName,title,uid,nickname,coverLarge,playKind,loginName,albumIdMin from NovelCollect Where playKind = '%@' And loginName = '%@'",playKind,loginName];
    sqlite3 *db = [DB openDB];
    NSMutableArray *array = [NSMutableArray array];
    // 定义执行结果接收者
    sqlite3_stmt *stmt = nil;
    // 我们查询的结果就在stmt里面;
    int result = sqlite3_prepare(db, [str UTF8String], -1, &stmt, nil);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW){
            int albumId = sqlite3_column_int(stmt, 0);
            int categoryId = sqlite3_column_int(stmt, 1);
            const unsigned char *categoryName1 = sqlite3_column_text(stmt, 2);
            const unsigned char *title1 = sqlite3_column_text(stmt, 3);
            int uid = sqlite3_column_int(stmt, 4);
            const unsigned char *nickname1 = sqlite3_column_text(stmt, 5);
            const unsigned char *coverLarge1 = sqlite3_column_text(stmt, 6);
            const unsigned char *playKind1 = sqlite3_column_text(stmt, 7);
            const unsigned char *loginName1 = sqlite3_column_text(stmt, 8);
            int albumIdMin = sqlite3_column_int(stmt, 9);
            
            NSString *categoryName = [NSString stringWithUTF8String:(const char *)categoryName1];
            NSString *title = [NSString stringWithUTF8String:(const char *)title1];
            NSString *nickname = [NSString stringWithUTF8String:(const char *)nickname1];
            NSString *coverLarge = [NSString stringWithUTF8String:(const char *)coverLarge1];
            NSString *playKind = [NSString stringWithUTF8String:(const char *)playKind1];
            NSString *loginName = [NSString stringWithUTF8String:(const char *)loginName1];
            
            NovelModel *model = [NovelModel novelModelWithAlbumId:albumId categoryId:categoryId categoryName:categoryName title:title uid:uid nickname:nickname coverLarge:coverLarge playKind:playKind loginName:loginName albumIdMin:albumIdMin];
            [array addObject:model];
        }
    }
    return array;
}
//模糊查询Id和用户
+ (NSArray *)selectNameAlbumId:(NSInteger)albumId loginName:(NSString *)loginName{
    NSString *str = [NSString stringWithFormat:@"select albumId,categoryId,categoryName,title,uid,nickname,coverLarge,playKind,loginName,albumIdMin from NovelCollect Where albumid LIKE '%%%ld%%' AND loginName = '%@'",albumId,loginName];
    sqlite3 *db = [DB openDB];
    NSMutableArray *array = [NSMutableArray array];
    // 定义执行结果接收者
    sqlite3_stmt *stmt = nil;
    // 我们查询的结果就在stmt里面;
    int result = sqlite3_prepare(db, [str UTF8String], -1, &stmt, nil);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW){
            int albumId = sqlite3_column_int(stmt, 0);
            int categoryId = sqlite3_column_int(stmt, 1);
            const unsigned char *categoryName1 = sqlite3_column_text(stmt, 2);
            const unsigned char *title1 = sqlite3_column_text(stmt, 3);
            int uid = sqlite3_column_int(stmt, 4);
            const unsigned char *nickname1 = sqlite3_column_text(stmt, 5);
            const unsigned char *coverLarge1 = sqlite3_column_text(stmt, 6);
            const unsigned char *playKind1 = sqlite3_column_text(stmt, 7);
            const unsigned char *loginName1 = sqlite3_column_text(stmt, 8);
            int albumIdMin = sqlite3_column_int(stmt, 9);

            
            NSString *categoryName = [NSString stringWithUTF8String:(const char *)categoryName1];
            NSString *title = [NSString stringWithUTF8String:(const char *)title1];
            NSString *nickname = [NSString stringWithUTF8String:(const char *)nickname1];
            NSString *coverLarge = [NSString stringWithUTF8String:(const char *)coverLarge1];
            NSString *playKind = [NSString stringWithUTF8String:(const char *)playKind1];
            NSString *loginName = [NSString stringWithUTF8String:(const char *)loginName1];
            
            NovelModel *model = [NovelModel novelModelWithAlbumId:albumId categoryId:categoryId categoryName:categoryName title:title uid:uid nickname:nickname coverLarge:coverLarge playKind:playKind loginName:loginName albumIdMin:albumIdMin];
            [array addObject:model];
        }
    }
    return array;
}

//模糊查询Id
+ (NSArray *)selectNameAlbumId:(NSInteger)albumId{
    NSString *str = [NSString stringWithFormat:@"select albumId,categoryId,categoryName,title,uid,nickname,coverLarge,playKind,loginName,albumIdMin from NovelCollect Where albumid LIKE '%%%ld%%'",albumId];
    sqlite3 *db = [DB openDB];
    NSMutableArray *array = [NSMutableArray array];
    // 定义执行结果接收者
    sqlite3_stmt *stmt = nil;
    // 我们查询的结果就在stmt里面;
    int result = sqlite3_prepare(db, [str UTF8String], -1, &stmt, nil);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW){
            int albumId = sqlite3_column_int(stmt, 0);
            int categoryId = sqlite3_column_int(stmt, 1);
            const unsigned char *categoryName1 = sqlite3_column_text(stmt, 2);
            const unsigned char *title1 = sqlite3_column_text(stmt, 3);
            int uid = sqlite3_column_int(stmt, 4);
            const unsigned char *nickname1 = sqlite3_column_text(stmt, 5);
            const unsigned char *coverLarge1 = sqlite3_column_text(stmt, 6);
            const unsigned char *playKind1 = sqlite3_column_text(stmt, 7);
            const unsigned char *loginName1 = sqlite3_column_text(stmt, 8);
            int albumIdMin = sqlite3_column_int(stmt, 9);

            
            NSString *categoryName = [NSString stringWithUTF8String:(const char *)categoryName1];
            NSString *title = [NSString stringWithUTF8String:(const char *)title1];
            NSString *nickname = [NSString stringWithUTF8String:(const char *)nickname1];
            NSString *coverLarge = [NSString stringWithUTF8String:(const char *)coverLarge1];
            NSString *playKind = [NSString stringWithUTF8String:(const char *)playKind1];
            NSString *loginName = [NSString stringWithUTF8String:(const char *)loginName1];
            
            NovelModel *model = [NovelModel novelModelWithAlbumId:albumId categoryId:categoryId categoryName:categoryName title:title uid:uid nickname:nickname coverLarge:coverLarge playKind:playKind loginName:loginName albumIdMin:albumIdMin];
            [array addObject:model];
        }
    }
    return array;
}





@end
